﻿# -*- coding: utf-8 -*-
"""
Created on Thu Nov 16 15:51:05 2017

@author: leshuangjiang
"""

import xlwings as xw
from pandas import DataFrame
import re
import os

path = r'D:\文档\表格\IE科\生产制造单\制单'
path_lst = [os.path.join(path,i) for i in os.listdir(path)]

def place():
    sheet_range = last_sheet.range('a20:aa200').value
    df = DataFrame(sheet_range)
    material = df[df[0] == '裁剪物料\n编号'].index.tolist()[0]
    need = df[df[0] == '要求'].index.tolist()[0]
    fuliao_num = df[df[0] == '辅料编号'].index.tolist()[0]
    a = df.iloc[fuliao_num]
    beizhu_col = a[a.isin(['用料部位/备注'])].index.tolist()[0]
    new = df.iloc[material+1:need+1, [0, 1, beizhu_col-3, beizhu_col-1, beizhu_col-2, beizhu_col]]
    new.columns=[0,1,2,3,4,5]
    return new, material, fuliao_num, need

def getDict(start, end, n):
    dic = []
    for f in range(start,end,1):
        two = []
        one = new.loc[f:f].values[0]
        two.append(one[1])
        two.append(one[0])
        two.append(None)
        two.extend(one[2:5])
        two.extend([None,None])
        two.append(one[5])
        dic.append(two)
    dic = [f for f in dic if any(f)==True]
    for k,v in enumerate(dic):
        xb_muban.sheets[0].range('c'+str(k+n)).value = v[0]
        xb_muban.sheets[0].range('d'+str(k+n)).value = v[1:]
    return 'ok'

def getName():
    try:
        need_sheet = [i for i in xb.sheets if i.api.visible==-1 and "制单" in i.name][0]
        need_sheet.activate()
        return need_sheet
    except IndexError as e:
        print('sheet表名出现错误，请修改')
    finally:
        pass
    
def getModule():
#    xb_hong = app.books.open(r'D:\文档\表格\IE科\module.xlsm')
    xb.activate()
    module = last_sheet.api.pagesetup.centerheader
#    xb_hong.macro('odm').run()
    res = re.findall(r'CFOB|ODM|FOB', module)[0]
#    xb_hong.close()
    return res

app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = False

for i, x in enumerate(path_lst):
    try:
        name_kuanhao = re.findall(r'\\(\d*\d)', x)[0]
        print(name_kuanhao, '正在制作中…………')
        xb = app.books.open(x)
        xb_muban = app.books.open(r'D:\文档\表格\IE科\全品类模版.xlsx')
        last_sheet = getName()
        module = getModule()
        name = last_sheet.range('h1').value
        new, start1, start2, end = place()
        xb.close()
        xb_muban.sheets[0].range('d3').value = name_kuanhao
        xb_muban.sheets[0].range('d4').value = name
        xb_muban.sheets[0].range('k1').value = module
        xb_muban.sheets[0].range('m8').add_hyperlink(x,name_kuanhao)
        getDict(start1+2, start2, 8)
        getDict(start2+1, end, 31)
        xb_muban.save('D:\\kill\\'+ name_kuanhao +'.xlsx')
        print('第',i+1, '个', name_kuanhao, '完成了', '\n')
        xb_muban.close()
#        app.screen_updating = True
    except Exception as e:
        print(e)
        print('还剩',len(path_lst)-i-1, '个没有完成: ',name_kuanhao)
        continue
    finally:
        app.quit()
        xw.App.kill(app)
#os.system('taskkill /F /IM EXCEL.exe')
print('所有的都已经完成！')
#        last_sheet.activate()
#        getPicture()
#        xb_muban.sheets[0].activate()
#        try:
#            xb_muban.sheets[0].range('n2').api.select
#            xb_muban.sheets[0].api.pictures.paste
#        except Exception as e:
#            print(e)
#def getPicture():
#    last_sheet.range('a45:t120').color = (212, 236, 70)
#    last_sheet.range('a45:t120').api.select
#    return last_sheet.range('a45:t120').api.copy

